package com.borun.dao;

import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.borun.bean.Base;
import com.borun.bean.page.Page;
import com.borun.dao.base.Dao;
import com.borun.util.DBUtil;

public class BaseDao implements Dao<Base> {

	@Override
	public boolean add(Base model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "insert into t_base(basename) values(?)";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setString(1, model.getBasename());
		return psmt.executeUpdate() > 0;
	}

	@Override
	public boolean delete(Integer id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "delete from t_base where id = ?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, id);
		return psmt.executeUpdate() > 0;
	}

	@Override
	public boolean update(Base model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "update t_base set basename = ? where id = ?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setString(1, model.getBasename());
		psmt.setInt(2, model.getId());
		return psmt.executeUpdate() > 0;
	}

	@Override
	public Base findById(Integer id) throws SQLException {
		Base b = null;
		Connection conn = DBUtil.getConnection();
		String sql = "select id,basename from t_base where id = ?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, id);
		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			b = new Base();
			b.setId(rs.getInt("id"));
			b.setBasename(rs.getString("basename"));
		}
		return b;
	}

	@Override
	public Base findBy(Base model) throws SQLException {
		Base b = null;
		PreparedStatement psmt = null;
		Connection conn = DBUtil.getConnection();
		String sql = "select top 1 id,basename from t_base where 1 = 1 ";
		if (model.getBasename() != null) {
			sql += " and basename like ? ";
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, "%" + model.getBasename() + "%");
		}
		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			b = new Base();
			b.setId(rs.getInt("id"));
			b.setBasename(rs.getString("basename"));
		}
		return b;
	}

	@Override
	public List<Base> getList() throws SQLException {
		Connection conn = DBUtil.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt
				.executeQuery("select id,basename from t_base where 1 = 1 ");
		List<Base> baseList = new ArrayList<Base>();
		Base b = null;
		while (rs.next()) {
			b = new Base();
			b.setId(rs.getInt("id"));
			b.setBasename(rs.getString("basename"));
			baseList.add(b);
		}
		return baseList;
	}

	@Override
	public List<Base> getListBy(Base model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		String sql = "select id,basename from t_base where 1 = 1 ";
		if (model.getBasename() != null) {
			sql += " and basename like ? ";
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, "%" + model.getBasename() + "%");
		}
		ResultSet rs = psmt.executeQuery();
		List<Base> baseList = new ArrayList<Base>();
		Base b = null;
		while (rs.next()) {
			b = new Base();
			b.setId(rs.getInt("id"));
			b.setBasename(rs.getString("basename"));
			baseList.add(b);
		}
		return baseList;
	}

	@Override
	public List<Base> getPagedList(Page page) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = conn
				.prepareStatement("select id,basename from t_base where 1 = 1 limit ?,?");
		psmt.setInt(1, page.getPageIndex());
		psmt.setInt(2, page.getPageSize());
		ResultSet rs = psmt.executeQuery();
		List<Base> baseList = new ArrayList<Base>();
		Base b = null;
		while (rs.next()) {
			b = new Base();
			b.setId(rs.getInt("id"));
			b.setBasename(rs.getString("basename"));
			baseList.add(b);
		}
		return baseList;
	}

	@Override
	public List<Base> getPagedListBy(Page page, Base model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"select id,basename from t_base where 1 = 1 ");
		if (model.getBasename() != null) {
			sql.append(" and basename like ? ");
			map.add("%" + model.getBasename() + "%");
		}
		sql.append(" limit ?,?");
		map.add(page.getPageIndex());
		map.add(page.getPageSize());

		psmt = conn.prepareStatement(sql.toString());
		for (int i = 0; i < map.size(); i++) {
			psmt.setObject(i + 1, map.get(i));
		}

		ResultSet rs = psmt.executeQuery();
		List<Base> baseList = new ArrayList<Base>();
		Base b = null;
		while (rs.next()) {
			b = new Base();
			b.setId(rs.getInt("id"));
			b.setBasename(rs.getString("basename"));
			baseList.add(b);
		}
		return baseList;
	}

}
